package async;

import java.text.SimpleDateFormat;
import java.util.Calendar;

public class Test {
    public static void main(String[] args) {
        String cityId= "370100";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//获取当前日期

        Calendar calendar = Calendar.getInstance();
        String nowTime = sdf.format(calendar.getTime());
        int nowHour = calendar.get(Calendar.HOUR_OF_DAY);
        String nowStatusTime = nowTime + " " + (nowHour < 10 ? ("0" + nowHour) : nowHour) + ":59:59";//获取当前时间 用于查询断面对应的状态
        //河流首页   河流超标断面统计 以及 地图河流查询 查询时间由两天前的五个小时修改为查询当前时间的前八个小时
        //calendar.add(Calendar.DAY_OF_MONTH, -2);//获取当前日期的前两天
        //calendar.add(Calendar.HOUR_OF_DAY, -5);//
        calendar.add(Calendar.HOUR_OF_DAY, -8);//
        String time = sdf.format(calendar.getTime());
        int hour = calendar.get(Calendar.HOUR_OF_DAY);//24小时制
//		if(hour == 0){//24点
//			hour = 19;
//		}else if (hour == 1) {//凌晨1点
//			hour = 20;
//		}else if (hour == 2) {//凌晨2点
//			hour = 21;
//		}else if (hour == 3) {//凌晨3点
//			hour = 22;
//		}else if (hour == 4) {//凌晨4点
//			hour = 23;
//		}else if (hour == 5) {//凌晨5点
//			hour = 0;
//		}else{
//			hour = hour -5;
//		}
		/*int year = calendar.get(Calendar.YEAR);// 获取年份
		int month = calendar.get(Calendar.MONTH) + 1;// 获取月份
		int day = calendar.get(Calendar.DATE);// 获取日
		String dataFromTime = year + "-" + (month < 10 ? ("0" + month) : month) + "-" + (day < 10 ? ("0" + day) : day)
				+" " +(hour < 10 ? ("0" + hour) : hour)+":00:00";*/
        //获取当前日期（年月日小时 ） 的前五个小时
        String dataFromTime = time + " " + (hour < 10 ? ("0" + hour) : hour) + ":00:00";//查询两天前的五个小时用于查询最新时间对应的数据
        StringBuilder sql = new StringBuilder();
        //start  by   yu_xia 20170815 按监测物 分组  取每一项监测物的第一条
        //sql.append("SELECT  row_number() OVER ( ORDER BY  ")
        //  modify by yuxia 20170921 按城市分组，查询该城市对应的所有排口对应的有效数据
        //sql.append("SELECT  row_number() OVER (  partition by total.C0008_ITEM_DESC ORDER BY  ")
        // start modify by yuxia 20180416 17:34 根据最新数据，对应关系好像修改了。  sql.append("SELECT  row_number() OVER (  partition by  total.C0033_RSCODE,total.C0008_ITEM_DESC  ")
        sql.append("SELECT  row_number() OVER (  partition by  total.C0007_SUBSTATION_ID,total.C0008_ITEM_DESC  ")
                // start modify by yuxia 20180416 17:34 根据最新数据，对应关系好像修改了。
                //  end  by yuxia 20170921 按城市分组，查询该城市对应的所有排口对应的有效数据
                //end   by   yu_xia 20170815 按监测物 分组  取每一项监测物的第一条
                .append("	order  by	total.C1705_DATETIME DESC ")
                .append("	) rnum, ")
                .append("	total.C0008_ITEM_DESC item, total.C0001_ITEM_CODE itemCode,")
                .append("SUBSTRING ( ")
                .append("		CONVERT ( ")
                .append("			VARCHAR (100), ")
                .append("			total.C1705_DATETIME, ")
                .append("			24 ")
                .append("		), ")
                .append("		0, ")
                .append("		6 ")
                .append("	) TIME,")

                .append("	total.C1705_VALUE val, ")
                //.append("	total.C0033_RSNAME name, ")
                //modify  by yuxia 20171115 查询 T_SUBSTATION_WATER  表中的pname  用于结果集查询    与24小时查询顺序保持一致
                .append("  total.C0007_PNAME, total.KH_CITY,")
                //start modify by licy 20200715 地表水首页，当所属城市和考核城市相同时，不展示考核城市，不同时，展示考核城市。考核城市为空的为“十三五”趋势科研
                //.append(" total.C0028_RNAME + '-' + total.C0033_RSNAME+'(' + REPLACE(total.KH_CITY,'-','→')+')' name,total.C0038_ADMIN_CODE adminCode, ")
                .append(" (case when (total.KH_CITY = total.C0001_RIVER_DIRECTION  or total.KH_CITY = '') then total.C0028_RNAME + '-' + total.C0033_RSNAME else total.C0028_RNAME + '-' + total.C0033_RSNAME + '(' + REPLACE(total.KH_CITY, '-', '→') + ')' END ) as name,")
                //end modify by licy 20200715 地表水首页，当所属城市和考核城市相同时，不展示考核城市，不同时，展示考核城市。考核城市为空的为“十三五”趋势科研
                .append(" total.C0038_ADMIN_CODE adminCode, s.C0113_THIS_AIM stval, ")
                //.append("  	case when s.C0001_ITEM_CODE ='311' then  2 "
                //		+ " when (s.C0001_ITEM_CODE ='316'  or  s.C0001_ITEM_CODE ='314' ) then  40  end as  stval, ")
                .append("   total.C1705_DATETIME,"
                        + " total.status ,total.codStatus,total.nhStatus,total.mnStatus ,total.C0003_STCODE  cityCode, total.objective")
                .append(" FROM ")
                .append("	( ")
                .append("		SELECT ")
                //监测项目顺序修正
                .append("			CASE a.C0008_ITEM_DESC ")
                .append("			WHEN 'PH' THEN ")
                .append("			'pH' ")
                .append("			ELSE ")
                .append("			a.C0008_ITEM_DESC ")
                .append("			END AS C0008_ITEM_DESC, ")
                .append("			b.C1705_DATETIME, ")
                .append("			b.C1705_VALUE, ")
                .append("			c.C0033_RSNAME, t.KH_CITY,")
                .append("         code.C0028_RNAME  , con.C0001_RIVER_DIRECTION,  ")//新增河流名称 以及河流流向
                .append("			a.C0001_ITEM_CODE, ")
                .append("			c.C0033_RSCODE , c.C0028_RCODE,  t.C0007_SUBSTATION_ID, t.C0003_STCODE , t.C0007_PNAME,t.C0038_ADMIN_CODE, t.WQ_OBJECTIVE as objective,")
                //add by yu_xia 2017-07-10 增加断面 状态 以及cod   氨氮对应的断面仪器状态
                //.append(" ,d.C0202_NAME  as status")
                .append(" case when d.C0202_CODE != '311' and d.C0202_CODE != '316'  and d.C0202_CODE != '314' "
                        + " then  d.C0202_NAME  end  AS status , ")
                .append("    case  when  a.C0001_ITEM_CODE ='311' and   d.C0202_CODE = '311' then d.C0202_NAME end as nhStatus,")
                .append("  case  when  a.C0001_ITEM_CODE ='316' and  d.C0202_CODE = '316' then d.C0202_NAME end as codStatus ,")
                .append("  case  when  a.C0001_ITEM_CODE ='314' and  d.C0202_CODE = '314' then d.C0202_NAME end as mnStatus  ")
                //end  by yu_xia 2017-07-10  增加断面 状态
                .append("		FROM ")
                //.append("			T_AI a ")
                //.append("		INNER JOIN T_RSECTION_HOUR_AVG b ON a.C0008_PID = b.C0008_PID ")
                //.append("		INNER JOIN T_RS_CODE c ON a.C0007_SUBSTATION_ID = c.C0033_RSCODE ")
                //.append("		left  JOIN T_STATION_STATUS d ON d.C0007_SUBSTATION_ID = a.C0007_SUBSTATION_ID "
                //		+ " and d.C0202_CODE = a.C0008_PID and  '"+dataFromTime+"'  BETWEEN d.C0105_STARTTIME and d.C0105_ENDTIME  ")
                //add by yu_xia 2017-07-10 获取有效数据的最大时间修改为 获取当前日期的前4个小时内的最新数据
                //.append("		WHERE b.c1705_qcode = '0' and ")
                //.append("		WHERE  b.C1705_DATETIME >= '"+dataFromTime+"' and ")
                //end  by yu_xia 2017-07-10 获取有效数据的最大时间修改为 获取当前日期的前4个小时内的最新数据
                .append("			 T_SUBSTATION_WATER    t   WITH (READPAST) left join  T_AI a  on t.C0007_SUBSTATION_ID = a.C0007_SUBSTATION_ID ")
                .append(" left   JOIN  (select  *  from  T_RSECTION_HOUR_AVG   WITH(READPAST) "
                        //start modify by sunfeng 2018年10月8日14:50:29 排除不是（00,04,08,12,16,20）的数据
                        + "  where  C1705_DATETIME >= '" + dataFromTime + "' AND  DATEPART(hh, C1705_DATETIME) in (00,04,08,12,16,20) and    C1705_QCODE ='0' )  b ON  b.C0008_PID = a.C0008_PID")
                //end modify by 孙峰 2018年10月8日14:51:09  排除不是（00,04,08,12,16,20）的数据
                //start modify by licy 20180414 21:44 根据最新数据，对应关系好像修改了。
                //.append(" left   JOIN T_RS_CODE c ON t.C0007_SUBSTATION_ID = c.C0033_RSCODE ")
                .append(" left   JOIN T_RS_CODE c ON t.C0007_PCODE = c.C0033_RSCODE and t.C0028_RCODE = c.C0028_RCODE ")
                //end modify by licy 20180414 21:44 根据最新数据，对应关系好像修改了。
                //start modidy by yuxia20171023 新增河流代码表  查询河流名称
                .append("  left join T_RIVER_CODE  code  on  t.C0028_RCODE  = code.C0028_RCODE  ")
                //end modidy by yuxia20171023 新增河流代码表  查询河流名称
                //start modidy by yuxia20171024  新增河流流向表   查询河流流向
                .append("  left join T_RS_CONTROL   con on t.C0007_SUBSTATION_ID = con.C0007_SUBSTATION_ID   ")
                //end modidy by yuxia20171024 新增河流流向表   查询河流流向
                .append(" LEFT JOIN T_STATION_STATUS d WITH(READPAST) ON d.C0007_SUBSTATION_ID = t.C0007_SUBSTATION_ID  ")
                .append(" AND '" + nowStatusTime + "'  BETWEEN d.C0105_STARTTIME ")
                .append("	AND d.C0105_ENDTIME   	")
                //add by yu_xia 2017-08-16 查询断面状态以及 氨氮cod对应的断面仪器状态
                //.append(" AND d.C0201_TYPE_NAME = '断面状态' ")
                //modify by yuxia 20171117  断面状态查询 加断面仪器状态
                .append(" and  (d.C0201_TYPE_NAME ='断面状态' "
                        //+ " or d.C0201_TYPE_NAME ='断面仪器状态' "
                        + "  or ( d.C0202_CODE ='311' or d.C0202_CODE ='316'  or d.C0202_CODE ='314' )) ")
                //end  by yu_xia 2017-08-16 查询断面状态以及 氨氮cod对应的断面仪器状态
                //监测项目顺序修正
                .append("		where 	 a.C0008_ITEM_DESC IN ( ")
                .append("			'化学需氧量', ")
                .append("			'高锰酸盐指数', ")
                .append("			'氨氮','总磷','总氮' ")
                //  add by yuxia 20170921 河流首页修改  不检测五项监测物
                //.append("		,	'PH', ")
                //.append("			'水温', ")
                //.append("			'溶解氧', ")
                //.append("			'电导率', ")
                //.append("			'浑浊度' ")
                //  end by yuxia 20170921 河流首页修改  不检测五项监测物
                .append(" ) and t.C0041_SUB_TYPE='2' ");
        //.append(" And DATEPART(hour,C1705_DATETIME) = "+date.split(" ")[1].substring(0,2));
		/*if (stname != null && !"".equals(stname)) {
			sql.append("		AND c.C0033_RSNAME LIKE '%").append(stname).append("%' ");
		}*/
        //  modify by yuxia 20170921 按城市分组，查询该城市对应的所有排口对应的有效数据
		/*if (stationId != null && !"".equals(stationId)) {
			sql.append("		AND t.C0007_SUBSTATION_ID  =  '"+stationId+"' ");
		}*/
        if (cityId != null && !"".equals(cityId)) {
            sql.append("		AND t.C0003_STCODE  =  '" + cityId + "' ");
            //start delete by licy 20181205 统一屏蔽掉断面：潍坊小清河入海口,青岛入海口（站点已撤销），莱芜的角峪（站点已撤销）
//	        if("370700".equals(cityId)){//当查询潍坊所对应的断面时，将小清河入海口剔除
//	        	sql.append("		AND t.C0007_SUBSTATION_ID  !=  '13092' ");
//	        }
            //end delete by licy 20181205 统一屏蔽掉断面：潍坊小清河入海口,青岛入海口，莱芜的角峪  （站点已撤销）
        }

        //start add by licy 20181205 统一屏蔽掉断面：潍坊小清河入海口,青岛入海口，莱芜的角峪  （站点已撤销）
        sql.append(" AND t.C0007_SUBSTATION_ID  not in ('13092','3024') ");
        //end add by licy 20181205 统一屏蔽掉断面：潍坊小清河入海口,青岛入海口，莱芜的角峪  （站点已撤销）

        //  end  by yuxia 20170921 按城市分组，查询该城市对应的所有排口对应的有效数据
        sql.append(" ) total ")
                //  add by yuxia 新增水质标准表
                //.append("LEFT JOIN T_WATER_STAND s ON total.C0033_RSCODE = s.C0007_SUBSTATION_ID ")
                // start modify by yuxia 20180416 17:34 根据最新数据，对应关系好像修改了。 .append("LEFT JOIN T_WATER_STAND_COUNT s  WITH(READPAST) ON total.C0033_RSCODE = s.C0007_SUBSTATION_ID ")
                .append("LEFT JOIN T_WATER_STAND_COUNT s  WITH(READPAST) ON total.C0007_SUBSTATION_ID = s.C0007_SUBSTATION_ID ")
                // end modify by yuxia 20180416 17:34 根据最新数据，对应关系好像修改了。
                //.append("AND s.C0003_YEAR = SUBSTRING (")
                .append("AND s.C0003_YEAR = SUBSTRING ('" + nowTime + "',0,5)  ")
                //.append("CONVERT ( VARCHAR (10),total.C1705_DATETIME,120 ),")
                //.append("0,5)")
                .append("AND total.C0001_ITEM_CODE = s.C0001_ITEM_CODE").toString();

				/*.append("	SELECT ")
				.append("		* ")
				.append("	FROM ")
				.append("		T_WATER_STAND ")
				.append("	WHERE ")
				.append("		C0003_YEAR = SUBSTRING ('")
				.append(date).append("', 0, 5) ")
				.append(") s ON total.C0033_RSCODE = s.C0007_SUBSTATION_ID ")
				.append("AND total.C0001_ITEM_CODE = s.C0001_ITEM_CODE ").toString()*/
				/*"ORDER BY " +
				"	total.C0008_ITEM_DESC, " +
				"	total.C1705_DATETIME DESC"*/
        ;
        //String topsql = "select top 10 r.* FROM ( " + sql + ") r where r.rnum > " + (page - 1) * 10;
        //start  by   yu_xia 20170815 按监测物 分组  取每一项监测物的第一条
        // modify by yuxia 20170921 按城市分组 查询每个城市对应的排口的最新有效数据
        //String topsql = "select top 10 r.* FROM ( " + sql + ") r where r.rnum   = 1  " ;
        String topsql = "select *,max(level) over(partition by c0007_pname order by level desc) as newLevel from ( select top 50 r.*,dbo.waterLevel (CASE WHEN r.itemCode=316 THEN r.val END,CASE WHEN r.itemCode=311 THEN r.val END,CASE WHEN r.itemCode=314 THEN r.val END) AS 'level' FROM ( " + sql + ") r where r.rnum   = 1 and r.name  is not null    ";
        // end  by yuxia 20170921 按城市分组 查询每个城市对应的排口的最新有效数据
        //end   by   yu_xia 20170815 按监测物 分组  取每一项监测物的第一条
        // topsql += " ORDER BY   r.cityCode, " +
        topsql += ") m ORDER BY   " +
                //"					r.name, " +
                " C0007_PNAME, " +//修改查询顺序与24小时 查询结果保持一致
                //监测项目顺序修正
                //"					r.rnum, " +

                "					C1705_DATETIME DESC, " +
//		        "					r.item " ;
                "					itemCode desc ; ";
        System.out.println(sql);
    }
}
